
ALTER proc sp_Change_Quotation_LineItem_Proc
(
	@LineItem_ID 	int, 
	@Quotation_ID 	int,  
	@Requested_Date datetime,
	@Quantity 	int,
	@Unit_Price 	float,
	@Amount 	float, 
	@User_ID 	varchar(10),
	@Is_Taxable 	int, 
	@Lineitem_Desc 	varchar(500),
	@Quoted_Qty 	int,
	@Lead_Time      varchar(50),
	@Quoted_Date	datetime,
	@line_item_loop int,
	@lineItem_update_status int,
    	@customer_partnumber varchar(50),
	@increment_status int output,
	@REQUESTED_PRICE 	FLOAT, 
	@CUST_RESALE_PRICE 	FLOAT, 
	@SUGG_RESALE_PRICE 	FLOAT,
	@PRODUCT_ID		INT,
	@IS_NEW_CUSTOMER_PART_NUMBER INT, --0 -TRUE 1 -FALSE
	@MIN_ORDER_QTY INT
)
AS

BEGIN
	DECLARE @OLD_CHANGE_NUMBER 	INT
	DECLARE @LINEITEM_CHANGE_NO 	INT
	DECLARE @CHANGE_LINEITEM_ID 	INT
	DECLARE @COMPARE_DESC 		VARCHAR(10)
    	DECLARE @DB_CUSTOMER_PART_NUMBER VARCHAR(50)
    	DECLARE @COMPARE_CUST_PART_NUMBER VARCHAR(10)
    
	DECLARE @db_qty int, @db_price float, @db_date datetime, @Is_Approved int, @db_quoted_qty int, @db_quoted_date datetime, @db_is_taxable int, @db_lineitem_desc varchar(500), @db_lead_time varchar(50)
	DECLARE @DB_REQUESTED_PRICE FLOAT 
	DECLARE @DB_CUST_RESALE_PRICE FLOAT 
	DECLARE @DB_SUGG_RESALE_PRICE FLOAT
	DECLARE @DB_QLI_ACTUAL_QTY INT
	DECLARE @DB_MIN_ORDER_QTY INT
	DECLARE @CUSTOMER_ID INT
	
	SELECT @CUSTOMER_ID = CUSTOMER_ID FROM QUOTATION_TABLE WHERE Quotation_ID = @Quotation_ID
	
	IF ( @IS_NEW_CUSTOMER_PART_NUMBER IS NOT NULL AND @IS_NEW_CUSTOMER_PART_NUMBER = 0 AND LEN(LTRIM(RTRIM(@customer_partnumber))) > 0 )
	 BEGIN
		IF ( (SELECT COUNT(*) CNT FROM CUSTOMER_PARTNUMBERS WHERE CUSTOMER_ID = @CUSTOMER_ID AND PRODUCT_ID = @product_id AND CUSTOMER_PARTNUMBER = LTRIM(RTRIM(@customer_partnumber)) AND ODM_ID IS NULL) = 0 )
		 BEGIN
			INSERT INTO CUSTOMER_PARTNUMBERS( CUSTOMER_ID, PRODUCT_ID, CUSTOMER_PARTNUMBER, LABEL_NUMBER ) VALUES( @CUSTOMER_ID, @product_id, LTRIM(RTRIM(@customer_partnumber)), '' )
	 	 END
	 END
	
	SELECT @db_qty = QLI_Qty, @db_price = QLI_Unit_Cost, @db_date = QLI_Requested_Date ,@db_quoted_qty = ISNULL( QLI_Quoted_Qty, 0 ), @db_quoted_date = ISNULL( QLI_Quoted_Date, 0000-00-00 ), 
	@db_is_taxable = ISNULL( QLI_Taxable,-1 ), @db_lineitem_desc = QLI_Description, @DB_CUSTOMER_PART_NUMBER = QLI_CUSTOMER_PARTNUMBER, @db_lead_time=QLI_LEAD_TIME, @DB_REQUESTED_PRICE = REQUESTED_PRICE,
	@DB_CUST_RESALE_PRICE = CUST_RESALE_PRICE, @DB_SUGG_RESALE_PRICE = SUGG_RESALE_PRICE, @DB_QLI_ACTUAL_QTY = QLI_ACTUAL_QTY, @DB_MIN_ORDER_QTY = MIN_ORDER_QTY
	FROM QUOTATION_ORDER_LINEITEM WHERE Quotation_ID = @Quotation_ID AND LineItem_ID = @LineItem_ID			

	if( ( @Lineitem_Desc <> @db_lineitem_desc ) or ( @db_lineitem_desc IS NULL ) )
  	begin
    		set @COMPARE_DESC = 'TRUE'
  	end
  	else
  	begin
    		set @COMPARE_DESC = 'FALSE'
  	end

  	if( ( @customer_partnumber <> @DB_CUSTOMER_PART_NUMBER ) or ( @DB_CUSTOMER_PART_NUMBER IS NULL ) )
  	begin
    		set @COMPARE_CUST_PART_NUMBER = 'TRUE'
  	end
  	else
  	begin
    		set @COMPARE_CUST_PART_NUMBER = 'FALSE'
  	end

	--FOR LAGECY ORDERS	
 	SELECT @OLD_CHANGE_NUMBER = CHANGE_NUMBER FROM QUOTATION_CHANGE_ORDER_LINEITEM WHERE QUOTATION_ID = @Quotation_ID AND LINEITEM_ID = @LineItem_ID
 	IF( @OLD_CHANGE_NUMBER IS NULL )
 	BEGIN
		INSERT Quotation_Change_Order_LineItem ( LineItem_ID, Quotation_ID, Change_Number, Line_Item_No, Product_ID, Qty, Unit_Cost, Amount, Requested_Date, Booking_Date, Status, Change_By_ID, Change_By_Name, Taxable, PO_Line_Item_No, TimeStamp, Transfer, Is_Approved, Quoted_Qty, Quoted_Date, lead_time, REQUESTED_PRICE, CUST_RESALE_PRICE, SUGG_RESALE_PRICE, QLI_ACTUAL_QTY, MIN_ORDER_QTY )
		( SELECT LineItem_ID, Quotation_ID, 0, QLI_Line_Item_No, Product_ID, QLI_Qty, QLI_Unit_Cost, QLI_Amount, QLI_Requested_Date, QLI_Booking_Date, QLI_Status, QLI_Added_By_ID, QLI_Added_By_Name, QLI_Taxable , QLI_PO_Line_Item_No, TimeStamp, Transfer, Is_Approved, QLI_Quoted_Qty, QLI_Quoted_Date, QLI_LEAD_TIME, REQUESTED_PRICE, CUST_RESALE_PRICE, SUGG_RESALE_PRICE, QLI_ACTUAL_QTY, MIN_ORDER_QTY FROM Quotation_Order_LineItem WHERE Quotation_ID = @Quotation_ID AND LineItem_ID = @LineItem_ID )
 	END

	/*
	UPDATE QUOTATION_ORDER_LINEITEM SET
		QLI_Requested_Date = @Requested_Date ,
		QLI_Qty = @Quantity ,
		QLI_Unit_Cost = @Unit_Price , 
		QLI_Amount = @Amount , 
		QLI_Last_Updated_By_ID = @User_ID ,
		QLI_Last_Updated_By_Name = @User_ID ,
		QLI_Taxable = @Is_Taxable ,
		QLI_Description = @Lineitem_Desc,
		QLI_Quoted_Qty = @Quoted_Qty,
		QLI_Quoted_Date = @Quoted_Date,
		QLI_CUSTOMER_PARTNUMBER = @customer_partnumber
	WHERE Quotation_ID = @Quotation_ID AND LineItem_ID = @LineItem_ID
	*/	
	SELECT @LINEITEM_CHANGE_NO = ISNULL( MAX( CHANGE_NUMBER ), 0 ) FROM Quotation_Change_Order_LineItem WHERE QUOTATION_ID = @Quotation_ID AND LINEITEM_ID = @LineItem_ID

	if( ( @db_qty <> @Quantity ) or ( @db_price <> @Unit_Price ) or ( @db_date <> @Requested_Date ) or ( isnull(@db_lead_time,'') <> isnull(@lead_time,'') )
		OR ISNULL(@DB_REQUESTED_PRICE,0) <> ISNULL(@REQUESTED_PRICE,0) OR ISNULL(@DB_CUST_RESALE_PRICE,0) <> ISNULL(@CUST_RESALE_PRICE,0) 
		OR ISNULL(@DB_SUGG_RESALE_PRICE,0) <> ISNULL(@SUGG_RESALE_PRICE,0)
		OR ISNULL(@DB_MIN_ORDER_QTY,0) <> ISNULL(@MIN_ORDER_QTY,0) )
	begin
		SET @DB_QLI_ACTUAL_QTY = @DB_QLI_ACTUAL_QTY + (@Quantity-@db_qty)
		SET @LINEITEM_CHANGE_NO = @LINEITEM_CHANGE_NO + 1

		INSERT Quotation_Change_Order_LineItem ( LineItem_ID, Quotation_ID, Change_Number, Line_Item_No, Product_ID, 
		Qty, Unit_Cost, Amount, Requested_Date, Booking_Date, Status, Change_By_ID, Change_By_Name, Taxable, 
		PO_Line_Item_No, TimeStamp, Transfer, Is_Approved, Quoted_Qty, Quoted_Date, lead_time, REQUESTED_PRICE, CUST_RESALE_PRICE, SUGG_RESALE_PRICE, QLI_ACTUAL_QTY, MIN_ORDER_QTY )
		( SELECT LineItem_ID, Quotation_ID, @LINEITEM_CHANGE_NO, QLI_Line_Item_No, Product_ID, 
		@Quantity, @Unit_Price, @Amount, @Requested_Date, QLI_Booking_Date, QLI_Status, @User_ID, @User_ID, @Is_Taxable, 
		QLI_PO_Line_Item_No, CURRENT_TIMESTAMP, Transfer, Is_Approved, QLI_Quoted_Qty, QLI_Quoted_Date, QLI_LEAD_TIME, REQUESTED_PRICE, CUST_RESALE_PRICE, SUGG_RESALE_PRICE, @DB_QLI_ACTUAL_QTY, MIN_ORDER_QTY FROM Quotation_Order_LineItem WHERE Quotation_ID = @Quotation_ID AND LineItem_ID = @LineItem_ID )
		
 		SELECT @CHANGE_LINEITEM_ID = (SELECT @@IDENTITY)

		UPDATE QUOTATION_ORDER_LINEITEM SET
			QLI_Requested_Date = @Requested_Date ,
			QLI_Qty = @Quantity ,
			QLI_Unit_Cost = @Unit_Price , 
			QLI_Amount = @Amount, 
			QLI_Last_Updated_By_ID = @User_ID,
			QLI_Last_Updated_By_Name = @User_ID,
			QLI_LEAD_TIME= @Lead_Time,			
			Is_Approved = 1,
			TimeStamp = CURRENT_TIMESTAMP,
			QLI_CUSTOMER_PARTNUMBER = @customer_partnumber,
			REQUESTED_PRICE = @REQUESTED_PRICE,
			CUST_RESALE_PRICE = @CUST_RESALE_PRICE, 
			SUGG_RESALE_PRICE = @SUGG_RESALE_PRICE,
			QLI_ACTUAL_QTY = @DB_QLI_ACTUAL_QTY,
			MIN_ORDER_QTY = @MIN_ORDER_QTY
		WHERE Quotation_ID = @Quotation_ID AND LineItem_ID = @LineItem_ID
		
		--UPDATE QUOTATION_ORDER_LINEITEM SET Is_Approved = 1 WHERE Quotation_ID = @Quotation_ID AND LineItem_ID = @LineItem_ID	
	end	

	IF( ( @db_quoted_qty <> @Quoted_Qty ) or ( @db_quoted_date <>  ISNULL(@Quoted_Date,0000-00-00) ) or ( @db_is_taxable <> @Is_Taxable )  or ( @compare_desc = 'TRUE' ) or ( @COMPARE_CUST_PART_NUMBER = 'TRUE' ) or ( isnull(@db_lead_time,'') <> isnull(@lead_time,'') ) )
	BEGIN

		UPDATE QUOTATION_ORDER_LINEITEM SET
			QLI_Description = @Lineitem_Desc,
			QLI_Taxable = @Is_Taxable ,
			QLI_Quoted_Qty 	= @Quoted_Qty,
			QLI_Quoted_Date = @Quoted_Date,
			QLI_LEAD_TIME	= @Lead_Time,
			TimeStamp = CURRENT_TIMESTAMP
		WHERE Quotation_ID = @Quotation_ID AND LineItem_ID = @LineItem_ID

		UPDATE Quotation_Change_Order_LineItem SET
			Taxable = @Is_Taxable ,
			Quoted_Qty = @Quoted_Qty,
			Quoted_Date = @Quoted_Date,
			Lead_Time   = @Lead_Time,
			TimeStamp = CURRENT_TIMESTAMP
		WHERE LineItem_ID = @LineItem_ID and Change_Number = @LINEITEM_CHANGE_NO
	END

	IF( ( @db_qty <> @Quantity ) or ( @db_price <> @Unit_Price ) or ( @db_date <> @Requested_Date ) or ( @db_quoted_qty <> @Quoted_Qty ) or ( @db_quoted_date <>  ISNULL(@Quoted_Date,0000-00-00) ) or ( @db_is_taxable <> @Is_Taxable ) or ( isnull(@db_lead_time,'') <> isnull(@lead_time,'') ) 
		OR ISNULL(@DB_REQUESTED_PRICE,0) <> ISNULL(@REQUESTED_PRICE,0) OR ISNULL(@DB_CUST_RESALE_PRICE,0) <> ISNULL(@CUST_RESALE_PRICE,0) 
		OR ISNULL(@DB_SUGG_RESALE_PRICE,0) <> ISNULL(@SUGG_RESALE_PRICE,0)
		OR ISNULL(@DB_MIN_ORDER_QTY,0) <> ISNULL(@MIN_ORDER_QTY,0) )
	BEGIN
		set @increment_status = 1
	 	set @lineItem_update_status = 1	
	END

	if( @line_item_loop = 1 and @lineItem_update_status = 1 )
	BEGIN		
		DECLARE @db_revision int
		SELECT @db_revision =  Q_Revision FROM QUOTATION_TABLE WHERE Quotation_ID = @Quotation_ID  
		IF ( @db_revision IS NULL )
		BEGIN
			SET @db_revision = 0				
		END

		UPDATE QUOTATION_TABLE SET Q_Revision = @db_revision + 1 WHERE Quotation_ID = @Quotation_ID
		
	END

END
